package com.luoqy.speedy.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.PostConstruct;
import javax.xml.crypto.Data;

import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.luoqy.speedy.common.ConfigManage;
import com.luoqy.speedy.core.base.service.LogRecordService;

/**
 * @author qf sql语句执行
 */
@Component
public class MySql{
	@Autowired
	private static LogRecordService logRecordService;
	@Autowired
	private  LogRecordService logRecordServices;
	@PostConstruct
	public void init(){
		logRecordService=logRecordServices;
	}
	/**
	 * @param sqlStr 执行SQL语句
	 * @param dataBaseList 数据库资料，不填写设为null则为默认 多数据源操作时进行设定
	 * @return Object map集合
	 */
	public static <T> List<T> SELECTLIST(String sqlStr,Class clazz,DataBase... dataBaseList) {
		Connection conn = null;
		Statement stmt = null;
		try {
			DataBase dataBase=new DataBase();
			if(dataBaseList.length>0){
				dataBase=dataBaseList[0];
			}
			String url = "";
			String user = "";
			String password = "";
			String driverClass = "";
			if (null==dataBase||null == dataBase.getUrl()||"".equals(dataBase.getUrl())) {
				// 读取配置文件
				Map<String,String> props= ConfigManage.dataConfig;
				//初始化的时候回查询不到
				if (null == props||props.size()==0) {
					return null;
				} else {
					// 加载文件
					// 读取信息
					url = props.get("url");
					user = props.get("account");
					password = props.get("password");
					driverClass = props.get("driverClass");
					
					// 注册驱动程序
					Class.forName(driverClass);
				}
			} else {
				url = dataBase.getUrl();
				user = dataBase.getAccount();
				password = dataBase.getPassword();
				driverClass = dataBase.getDriverClass();
				// 注册驱动程序
				Class.forName(driverClass);
			}

			// 打开链接
			conn = (Connection) DriverManager.getConnection(url, user, password);
			// 执行查询
			stmt = (Statement) conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(sqlStr);
			//排除初始化查询库语句
			boolean flag=true;
			if(sqlStr.contains("information_schema.SCHEMATA")){
				flag=false;
			}
			//记录执行SQL
			if(flag){
				Map<String,String> sqlLog=new HashMap<String,String>();
				sqlLog.put("sqlstr", sqlStr);
				sqlLog.put("uid", UUID.randomUUID().toString().replaceAll("-",""));
				logRecordService.add(sqlLog);
			}
			// 获取结果集信息
			ResultSetMetaData resultSetMD = resultSet.getMetaData();
			// 总共多少条值
			List<Map<String, String>> listData = new ArrayList<Map<String, String>>();
			while (resultSet.next()) {
				Map<String, String> map = new HashMap<String, String>();

				for (int i = 1; i < resultSetMD.getColumnCount() + 1; i++) {
					// 字段名
					String name = resultSetMD.getColumnName(i);
					// 值
					String value = resultSet.getObject(i) + "";
					if (value.equals("null")) {
						value = "";
					}
					// 处理时间尾缀问题
					if (name.contains("time")) {
						value = value.split("\\.")[0];
					}
					map.put(underlineToHump(name), value);
				}
				listData.add(map);
			}
			return JSON.parseArray(JSON.toJSONString(listData),clazz);
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
			throw new RuntimeException("数据源配置信息异常，需要进行配置131=="+e.getMessage());
			// AllException.recordException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException se2) {
				throw new RuntimeException("数据源配置信息异常，需要进行配置140==stmt");
			} // 什么都不做
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				throw new RuntimeException("数据源配置信息异常，需要进行配置147==conn");
			}
		}

	}

	/**
	 *
	 * @param sqlStr
	 * @param clazz
	 * @param dataBaseList
	 * @param <T>
	 * @return
	 */
	public  static <T> T SELECTONE(String sqlStr, Class<T> clazz, DataBase... dataBaseList) {
		Connection conn = null;
		Statement stmt = null;
		try {
			DataBase dataBase=new DataBase();
			if(dataBaseList.length>0){
				dataBase=dataBaseList[0];
			}
			String url = "";
			String user = "";
			String password = "";
			String driverClass = "";
			if (null==dataBase||null == dataBase.getUrl()||"".equals(dataBase.getUrl())) {
				// 读取配置文件
				Map<String,String> props= ConfigManage.dataConfig;
				//初始化的时候回查询不到
				if (null == props||props.size()==0) {
					return null;
				} else {
					// 加载文件
					// 读取信息
					url = props.get("url");
					user = props.get("account");
					password = props.get("password");
					driverClass = props.get("driverClass");

					// 注册驱动程序
					Class.forName(driverClass);
				}
			} else {
				url = dataBase.getUrl();
				user = dataBase.getAccount();
				password = dataBase.getPassword();
				driverClass = dataBase.getDriverClass();
				// 注册驱动程序
				Class.forName(driverClass);
			}

			// 打开链接
			conn = (Connection) DriverManager.getConnection(url, user, password);
			// 执行查询
			stmt = (Statement) conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(sqlStr);
			//排除初始化查询库语句
			boolean flag=true;
			if(sqlStr.contains("information_schema.SCHEMATA")){
				flag=false;
			}
			//记录执行SQL
			if(flag){
				Map<String,String> sqlLog=new HashMap<String,String>();
				sqlLog.put("sqlstr", sqlStr);
				sqlLog.put("uid", UUID.randomUUID().toString().replaceAll("-",""));
				logRecordService.add(sqlLog);
			}
			// 获取结果集信息
			ResultSetMetaData resultSetMD = resultSet.getMetaData();
			// 总共多少条值
			Map<String, String> map = new HashMap<String, String>();
			while (resultSet.next()) {
				for (int i = 1; i < resultSetMD.getColumnCount() + 1; i++) {
					// 字段名
					String name = resultSetMD.getColumnName(i);
					// 值
					String value = resultSet.getObject(i) + "";
					if (value.equals("null")) {
						value = "";
					}
					// 处理时间尾缀问题
					if (name.contains("time")) {
						value = value.split("\\.")[0];
					}
					map.put(underlineToHump(name), value);
				}
			}
			// 是否获取单个值
			return JSON.parseObject(JSON.toJSONString(map),clazz);
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
			throw new RuntimeException("数据源配置信息异常，需要进行配置131=="+e.getMessage());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException se2) {
				throw new RuntimeException("数据源配置信息异常，需要进行配置140==stmt");
			} // 什么都不做
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				throw new RuntimeException("数据源配置信息异常，需要进行配置147==conn");
			}
		}

	}
	/**
	 * @param sqlStr
	 *            [sql语句，增删改查共用]
	 * @param dataBaseList 数据库资料，不填写设为null则为默认 多数据源操作时进行设定
	 * @return 返回通过sql查询的结果集
	 */
	public static int EXECUTE(String sqlStr, DataBase... dataBaseList) {
		Connection conn = null;
		Statement stmt = null;
		try {
			DataBase dataBase=new DataBase();
			if(dataBaseList.length>0){
				dataBase=dataBaseList[0];
			}
			String url = "";
			String user = "";
			String password = "";
			String driverClass = "";
			if (null == dataBase||null == dataBase.getUrl()||"".equals(dataBase.getUrl())) {
				// 读取配置文件
				Map<String,String> props= ConfigManage.dataConfig;
				if (null == props||props.size()==0) {
					return 0;
				} else {
					// 加载文件
					// 读取信息
					url = props.get("url");
					user = props.get("account");
					password = props.get("password");
					driverClass = props.get("driverClass");
					// 注册驱动程序
					Class.forName(driverClass);
				}
			} else {
				url = dataBase.getUrl();
				user = dataBase.getAccount();
				password = dataBase.getPassword();
				driverClass = dataBase.getDriverClass();
				// 注册驱动程序
				Class.forName(driverClass);
			}

			// 打开链接
			conn = (Connection) DriverManager.getConnection(url, user, password);
			// 执行查询
			stmt = (Statement) conn.createStatement();
			int rs = stmt.executeUpdate(sqlStr,Statement.RETURN_GENERATED_KEYS);
			//记录执行SQL
			Map<String,String> sqlLog=new HashMap<String,String>();
			sqlLog.put("sqlstr", sqlStr);
			sqlLog.put("uid", UUID.randomUUID().toString().replaceAll("-",""));
			logRecordService.add(sqlLog);
			//处理结果
			if(sqlStr.contains("insert")){
                ResultSet rsKeys=stmt.getGeneratedKeys();
                if (rs > 0) {
                    if(rsKeys.next()){
                        return rsKeys.getInt(1);
                    } else{
                        return 0;
                    }
                } else {
                    return 0;
                }
            }else{
			    return rs;
            }

		} catch (Exception e) {
			// 处理 Class.forName 错误
			System.err.println("数据源配置信息异常，需要进行配置220");
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭资源
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException se2) {
				System.err.println(se2.getMessage());
				se2.printStackTrace();
			} // 什么都不做
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				System.err.println(se.getMessage());
				se.printStackTrace();
			}
		}
	}

	/***
	 * <p>
	 * 将驼峰转为下划线
	 * </p >
	 * @param str
	 * @return java.lang.String
	 * @author comapss
	 * @date 2022/5/9 0:01
	 * @since 1.0.0
	 **/
	private static final String humpToUnderlineStr="[A-Z]";
	private static String humpToUnderline(String str) {
		Pattern compile = Pattern.compile(humpToUnderlineStr);
		Matcher matcher = compile.matcher(str);
		StringBuffer sb = new StringBuffer();
		while(matcher.find()) {
			matcher.appendReplacement(sb,  "_" + matcher.group(0).toLowerCase());
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	/***
	 * <p>
	 * 将下划线转为驼峰
	 * </p >
	 * @param str
	 * @return java.lang.String
	 * @author comapss
	 * @date 2022/5/9 0:01
	 * @since 1.0.0
	 **/
	private static final String underlineToHumpStr="_[a-z]";
	private static String underlineToHump(String str) {
		str = str.toLowerCase();
		Pattern compile = Pattern.compile(underlineToHumpStr);
		Matcher matcher = compile.matcher(str);
		StringBuffer sb = new StringBuffer();
		while(matcher.find()) {
			matcher.appendReplacement(sb,  matcher.group(0).toUpperCase().replace("_",""));
		}
		matcher.appendTail(sb);
		return sb.toString();
	}
}
